<html>
<!-- =====================================================================

  File:      Generated file for Adventure Works Cycles Storefront Sample
  Summary:   Self-documentation for application
  Date:	     June 16, 2003

=====================================================================

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) 2003 Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= -->
<head>
  <link rel=stylesheet href=style.css>
</head>
<body>
<div class=SourcePanel style="font-size:12">
<pre style="background-color:white">
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Configuration</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span>.<span style="color: #2040a0">SqlClient</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span>.<span style="color: #2040a0">OleDb</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">IO</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Xml</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Diagnostics</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Threading</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Globalization</span><span style="color: #4444FF">;</span>

<span style="color: #008000">/*=====================================================================

  File:      ProductsDB.cs for Adventure Works Cycles Storefront Sample
  Summary:   Middle tier component for browsing saleable items.
  Date:	     June 16, 2003

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */</span>

<span style="color: #0000FF"><strong>namespace</strong></span> <span style="color: #2040a0">Microsoft</span>.<span style="color: #2040a0">Samples</span>.<span style="color: #2040a0">SqlServer</span> 
<span style="color: #4444FF"><strong>{</strong></span>

    <span style="color: #008000">//*******************************************************</span>
    <span style="color: #008000">//</span>
    <span style="color: #008000">// ProductDetails Class</span>
    <span style="color: #008000">//</span>
    <span style="color: #008000">// A simple data class that encapsulates details about</span>
    <span style="color: #008000">// a particular product inside the AdventureWorks</span>
    <span style="color: #008000">// database.</span>
    <span style="color: #008000">//</span>
	<span style="color: #008000">// Note that it is bad style to expose public fields.  So </span>
	<span style="color: #008000">// instead we maintain private fields with corresponding</span>
	<span style="color: #008000">// public properties.  This helps avoid binary </span>
	<span style="color: #008000">// incompatabilities if the public fields were to have to</span>
	<span style="color: #008000">// be changed to properties later.</span>
	<span style="color: #008000">//</span>
	<span style="color: #008000">//*******************************************************</span>

    <span style="color: #0000FF"><strong>public</strong></span> <span style="color: #0000FF"><strong>class</strong></span> <span style="color: #2040a0">ProductDetails</span> 
	<span style="color: #4444FF"><strong>{</strong></span>
        <span style="color: #0000FF"><strong>private</strong></span> <span style="color: #2040a0">String</span>  <span style="color: #2040a0">modelNumber</span><span style="color: #4444FF">;</span>
        <span style="color: #0000FF"><strong>private</strong></span> <span style="color: #2040a0">String</span>  <span style="color: #2040a0">modelName</span><span style="color: #4444FF">;</span>
        <span style="color: #0000FF"><strong>private</strong></span> <span style="color: #2040a0">String</span>  <span style="color: #2040a0">productImage</span><span style="color: #4444FF">;</span>
        <span style="color: #0000FF"><strong>private</strong></span> <span style="color: #2040a0">String</span> <span style="color: #2040a0">unitCost</span><span style="color: #4444FF">;</span>
        <span style="color: #0000FF"><strong>private</strong></span> <span style="color: #2040a0">String</span>  <span style="color: #2040a0">description</span><span style="color: #4444FF">;</span>

		<span style="color: #008000">// The fields above are exposed publically as properties</span>
		<span style="color: #008000">// There is nothing fancy going on here, it is just better </span>
		<span style="color: #008000">// practice to not expose public fields.</span>

		<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">String</span> <span style="color: #2040a0">ModelNumber</span>
		<span style="color: #4444FF"><strong>{</strong></span>
			<span style="color: #2040a0">get</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">modelNumber</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
			<span style="color: #2040a0">set</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #2040a0">modelNumber</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">value</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
		<span style="color: #4444FF"><strong>}</strong></span>

		<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">String</span> <span style="color: #2040a0">ModelName</span>
		<span style="color: #4444FF"><strong>{</strong></span>
			<span style="color: #2040a0">get</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">modelName</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
			<span style="color: #2040a0">set</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #2040a0">modelName</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">value</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
		<span style="color: #4444FF"><strong>}</strong></span>

		<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">String</span> <span style="color: #2040a0">ProductImage</span>
		<span style="color: #4444FF"><strong>{</strong></span>
			<span style="color: #2040a0">get</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">productImage</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
			<span style="color: #2040a0">set</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #2040a0">productImage</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">value</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
		<span style="color: #4444FF"><strong>}</strong></span>

		<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">String</span> <span style="color: #2040a0">UnitCost</span>
		<span style="color: #4444FF"><strong>{</strong></span>
			<span style="color: #2040a0">get</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">unitCost</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
			<span style="color: #2040a0">set</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #2040a0">unitCost</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">value</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
		<span style="color: #4444FF"><strong>}</strong></span>


		<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">String</span> <span style="color: #2040a0">Description</span>
		<span style="color: #4444FF"><strong>{</strong></span>
			<span style="color: #2040a0">get</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">description</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
			<span style="color: #2040a0">set</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #2040a0">description</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">value</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
		<span style="color: #4444FF"><strong>}</strong></span>

    <span style="color: #4444FF"><strong>}</strong></span>

    <span style="color: #008000">//*******************************************************</span>
    <span style="color: #008000">//</span>
    <span style="color: #008000">// ProductsDB Class</span>
    <span style="color: #008000">//</span>
    <span style="color: #008000">// Business/Data Logic Class that encapsulates all data</span>
    <span style="color: #008000">// logic necessary to query products within</span>
    <span style="color: #008000">// the AdventureWorks database.</span>
    <span style="color: #008000">//</span>
    <span style="color: #008000">//*******************************************************</span>

    <span style="color: #0000FF"><strong>public</strong></span> <span style="color: #0000FF"><strong>class</strong></span> <span style="color: #2040a0">ProductsDB</span> <span style="color: #4444FF"><strong>{</strong></span>

        <span style="color: #008000">//*******************************************************</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// ProductsDB.GetProductTree() Method &lt;a name=&quot;GetProductTree&quot;&gt;&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// The GetProductCategories method returns a string that exposes all </span>
        <span style="color: #008000">// product categories and subcategories (and their CategoryIDs) within the Adventure Works Cycles   </span>
        <span style="color: #008000">// database.  The string contains this information formatted as XML, which</span>
		<span style="color: #008000">// is used in the menu tree control.</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// Other relevant sources:</span>
        <span style="color: #008000">//     + &lt;a href=&quot;usp_ProductCategoryList.htm&quot; style=&quot;color:green&quot;&gt;usp_ProductCategoryList Stored Procedure&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">//*******************************************************</span>

		<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">DataSet</span> <span style="color: #2040a0">GetProductTree</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span> 
		<span style="color: #4444FF"><strong>{</strong></span>

			<span style="color: #008000">// Create Instance of Connection and Command Object</span>
			<span style="color: #2040a0">SqlConnection</span> <span style="color: #2040a0">myConnection</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">null</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">try</span>
			<span style="color: #4444FF"><strong>{</strong></span>

				<span style="color: #2040a0">myConnection</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlConnection</span><span style="color: #4444FF">(</span><span style="color: #2040a0">Properties</span>.<span style="color: #2040a0">Settings</span>.<span style="color: #2040a0">Default</span>.<span style="color: #2040a0">ConnectionString</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">SqlCommand</span> <span style="color: #2040a0">myCommand</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlCommand</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;usp_ProductCategoryList&quot;</span>, <span style="color: #2040a0">myConnection</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

				<span style="color: #008000">// Mark the Command as a SPROC</span>
				<span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">CommandType</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CommandType</span>.<span style="color: #2040a0">StoredProcedure</span><span style="color: #4444FF">;</span>

				<span style="color: #008000">// Execute the command</span>
				<span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">Open</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">DataSet</span> <span style="color: #2040a0">productsDs</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">DataSet</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;Products&quot;</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">productsDs</span>.<span style="color: #2040a0">Locale</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CultureInfo</span>.<span style="color: #2040a0">InvariantCulture</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">SqlDataAdapter</span> <span style="color: #2040a0">productsDa</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlDataAdapter</span><span style="color: #4444FF">(</span><span style="color: #2040a0">myCommand</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">productsDa</span>.<span style="color: #2040a0">Fill</span><span style="color: #4444FF">(</span><span style="color: #2040a0">productsDs</span>, <span style="color: #444444">&quot;ProductCategory&quot;</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">productsDs</span>.<span style="color: #2040a0">Tables</span><span style="color: #4444FF">[</span><span style="color: #FF0000">1</span><span style="color: #4444FF">]</span>.<span style="color: #2040a0">TableName</span> <span style="color: #4444FF">=</span> <span style="color: #444444">&quot;ProductSubcategory&quot;</span><span style="color: #4444FF">;</span>
				<span style="color: #008000">//TODO: Switch sproc column name, then fix up relation column</span>
				<span style="color: #2040a0">DataRelation</span> <span style="color: #2040a0">categoryRel</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">DataRelation</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;CategorySubCategories&quot;</span>, <span style="color: #2040a0">productsDs</span>.<span style="color: #2040a0">Tables</span><span style="color: #4444FF">[</span><span style="color: #444444">&quot;ProductCategory&quot;</span><span style="color: #4444FF">]</span>.<span style="color: #2040a0">Columns</span><span style="color: #4444FF">[</span><span style="color: #444444">&quot;ProductCategoryID&quot;</span><span style="color: #4444FF">]</span>,
					<span style="color: #2040a0">productsDs</span>.<span style="color: #2040a0">Tables</span><span style="color: #4444FF">[</span><span style="color: #444444">&quot;ProductSubcategory&quot;</span><span style="color: #4444FF">]</span>.<span style="color: #2040a0">Columns</span><span style="color: #4444FF">[</span><span style="color: #444444">&quot;ProductCategoryID&quot;</span><span style="color: #4444FF">]</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">productsDs</span>.<span style="color: #2040a0">Relations</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">categoryRel</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">productsDs</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
			<span style="color: #2040a0">finally</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">myConnection</span> <span style="color: #4444FF">!</span><span style="color: #4444FF">=</span> <span style="color: #2040a0">null</span><span style="color: #4444FF">)</span> <span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">Close</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
		<span style="color: #4444FF"><strong>}</strong></span>

        <span style="color: #008000">//*******************************************************</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// ProductsDB.GetProducts() Method &lt;a name=&quot;GetProducts&quot;&gt;&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// The GetProducts method returns a DataSet which contains all products within a specified</span>
        <span style="color: #008000">// product category.  </span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// Other relevant sources:</span>
        <span style="color: #008000">//     + &lt;a href=&quot;usp_ProductsBySubcategory.htm&quot; style=&quot;color:green&quot;&gt;usp_ProductsBySubcategory Stored Procedure&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">//*******************************************************</span>

        <span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">SqlDataReader</span> <span style="color: #2040a0">GetProducts</span><span style="color: #4444FF">(</span><span style="color: #0000FF"><strong>int</strong></span> <span style="color: #2040a0">subCategoryID</span><span style="color: #4444FF">)</span> <span style="color: #4444FF"><strong>{</strong></span>

            <span style="color: #008000">// Create Instance of Connection and Command Object</span>
            <span style="color: #2040a0">SqlConnection</span> <span style="color: #2040a0">myConnection</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlConnection</span><span style="color: #4444FF">(</span><span style="color: #2040a0">Properties</span>.<span style="color: #2040a0">Settings</span>.<span style="color: #2040a0">Default</span>.<span style="color: #2040a0">ConnectionString</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">SqlCommand</span> <span style="color: #2040a0">myCommand</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">CreateCommand</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">CommandText</span> <span style="color: #4444FF">=</span> <span style="color: #444444">&quot;usp_ProductsBySubcategory&quot;</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Mark the Command as a SPROC</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">CommandType</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CommandType</span>.<span style="color: #2040a0">StoredProcedure</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Add Parameters to SPROC</span>
            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterSubcategoryID</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@SubcategoryID&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">Int</span>, <span style="color: #FF0000">4</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterSubcategoryID</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">subCategoryID</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterSubcategoryID</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

			<span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterCurrencyCode</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@Culture&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">10</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">parameterCurrencyCode</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CultureInfo</span>.<span style="color: #2040a0">CurrentUICulture</span>.<span style="color: #2040a0">Name</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterCurrencyCode</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            
            <span style="color: #008000">// Execute the stored procedure and Return the datareader result</span>
			<span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">Open</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">ExecuteReader</span><span style="color: #4444FF">(</span><span style="color: #2040a0">CommandBehavior</span>.<span style="color: #2040a0">CloseConnection</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
        <span style="color: #4444FF"><strong>}</strong></span>


        <span style="color: #008000">//*******************************************************</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// ProductsDB.GetProductDetails() Method &lt;a name=&quot;GetProductDetails&quot;&gt;&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// The GetProductDetails method returns a ProductDetails</span>
        <span style="color: #008000">// struct containing specific details about a specified</span>
        <span style="color: #008000">// product within the AdventureWorks Database.</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// Other relevant sources:</span>
        <span style="color: #008000">//     + &lt;a href=&quot;usp_ProductDetail.htm&quot; style=&quot;color:green&quot;&gt;usp_ProductDetail Stored Procedure&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">//*******************************************************</span>

        <span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">ProductDetails</span> <span style="color: #2040a0">GetProductDetails</span><span style="color: #4444FF">(</span><span style="color: #0000FF"><strong>int</strong></span> <span style="color: #2040a0">productID</span>, <span style="color: #2040a0">String</span> <span style="color: #2040a0">language</span><span style="color: #4444FF">)</span> <span style="color: #4444FF"><strong>{</strong></span>

            <span style="color: #008000">// Create Instance of Connection and Command Object</span>
            <span style="color: #2040a0">SqlConnection</span> <span style="color: #2040a0">myConnection</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlConnection</span><span style="color: #4444FF">(</span><span style="color: #2040a0">Properties</span>.<span style="color: #2040a0">Settings</span>.<span style="color: #2040a0">Default</span>.<span style="color: #2040a0">ConnectionString</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">SqlCommand</span> <span style="color: #2040a0">myCommand</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlCommand</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;usp_ProductDetail&quot;</span>, <span style="color: #2040a0">myConnection</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Mark the Command as a SPROC</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">CommandType</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CommandType</span>.<span style="color: #2040a0">StoredProcedure</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Add Parameters to SPROC</span>
            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterProductID</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@ProductID&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">Int</span>, <span style="color: #FF0000">4</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterProductID</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">productID</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterProductID</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

			<span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterLanguage</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@Language&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NChar</span>, <span style="color: #FF0000">2</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">parameterLanguage</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">language</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterLanguage</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

			<span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterCurrencyCode</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@Culture&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">10</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

			<span style="color: #2040a0">parameterCurrencyCode</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CultureInfo</span>.<span style="color: #2040a0">CurrentUICulture</span>.<span style="color: #2040a0">Name</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterCurrencyCode</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>


            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterModelNumber</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@ModelNumber&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">25</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterModelNumber</span>.<span style="color: #2040a0">Direction</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">ParameterDirection</span>.<span style="color: #2040a0">Output</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterModelNumber</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterModelName</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@ModelName&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">50</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterModelName</span>.<span style="color: #2040a0">Direction</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">ParameterDirection</span>.<span style="color: #2040a0">Output</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterModelName</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterProductImage</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@ProductImage&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">50</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterProductImage</span>.<span style="color: #2040a0">Direction</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">ParameterDirection</span>.<span style="color: #2040a0">Output</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterProductImage</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

			<span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterUnitCost</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@UnitCost&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">20</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">parameterUnitCost</span>.<span style="color: #2040a0">Direction</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">ParameterDirection</span>.<span style="color: #2040a0">Output</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterUnitCost</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>


            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterDescription</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@Description&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">3800</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterDescription</span>.<span style="color: #2040a0">Direction</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">ParameterDirection</span>.<span style="color: #2040a0">Output</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterDescription</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Open the connection and execute the Command</span>
			<span style="color: #2040a0">try</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">Open</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">ExecuteNonQuery</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
			<span style="color: #2040a0">finally</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">State</span> <span style="color: #4444FF">!</span><span style="color: #4444FF">=</span> <span style="color: #2040a0">ConnectionState</span>.<span style="color: #2040a0">Closed</span><span style="color: #4444FF">)</span>
					<span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">Close</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>

			<span style="color: #008000">//Fall back to US English descriptions if non-US English is not available</span>
			<span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">parameterDescription</span>.<span style="color: #2040a0">Value</span>.<span style="color: #2040a0">Equals</span><span style="color: #4444FF">(</span><span style="color: #2040a0">DBNull</span>.<span style="color: #2040a0">Value</span><span style="color: #4444FF">)</span><span style="color: #4444FF">)</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">language</span> <span style="color: #4444FF">=</span><span style="color: #4444FF">=</span> <span style="color: #444444">&quot;EN&quot;</span><span style="color: #4444FF">)</span>
					<span style="color: #2040a0">parameterDescription</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #444444">&quot;&quot;</span><span style="color: #4444FF">;</span>
				<span style="color: #0000FF"><strong>else</strong></span>
					<span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">GetProductDetails</span><span style="color: #4444FF">(</span><span style="color: #2040a0">productID</span>, <span style="color: #444444">&quot;EN&quot;</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>

            <span style="color: #008000">// Create and Populate ProductDetails Struct using</span>
            <span style="color: #008000">// Output Params from the SPROC</span>
            <span style="color: #2040a0">ProductDetails</span> <span style="color: #2040a0">myProductDetails</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">ProductDetails</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">myProductDetails</span>.<span style="color: #2040a0">ModelNumber</span> <span style="color: #4444FF">=</span> <span style="color: #4444FF">(</span><span style="color: #2040a0">string</span><span style="color: #4444FF">)</span><span style="color: #2040a0">parameterModelNumber</span>.<span style="color: #2040a0">Value</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myProductDetails</span>.<span style="color: #2040a0">ModelName</span> <span style="color: #4444FF">=</span> <span style="color: #4444FF">(</span><span style="color: #2040a0">string</span><span style="color: #4444FF">)</span><span style="color: #2040a0">parameterModelName</span>.<span style="color: #2040a0">Value</span><span style="color: #4444FF">;</span>
			<span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">parameterProductImage</span>.<span style="color: #2040a0">Value</span>.<span style="color: #2040a0">Equals</span><span style="color: #4444FF">(</span><span style="color: #2040a0">DBNull</span>.<span style="color: #2040a0">Value</span><span style="color: #4444FF">)</span><span style="color: #4444FF">)</span>
				<span style="color: #2040a0">myProductDetails</span>.<span style="color: #2040a0">ProductImage</span> <span style="color: #4444FF">=</span> <span style="color: #444444">&quot;unknown.gif&quot;</span><span style="color: #4444FF">;</span>
			<span style="color: #0000FF"><strong>else</strong></span>
				<span style="color: #2040a0">myProductDetails</span>.<span style="color: #2040a0">ProductImage</span> <span style="color: #4444FF">=</span> <span style="color: #4444FF">(</span><span style="color: #4444FF">(</span><span style="color: #2040a0">string</span><span style="color: #4444FF">)</span><span style="color: #2040a0">parameterProductImage</span>.<span style="color: #2040a0">Value</span><span style="color: #4444FF">)</span>.<span style="color: #2040a0">Trim</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myProductDetails</span>.<span style="color: #2040a0">UnitCost</span> <span style="color: #4444FF">=</span> <span style="color: #4444FF">(</span><span style="color: #2040a0">String</span><span style="color: #4444FF">)</span><span style="color: #2040a0">parameterUnitCost</span>.<span style="color: #2040a0">Value</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myProductDetails</span>.<span style="color: #2040a0">Description</span> <span style="color: #4444FF">=</span> <span style="color: #4444FF">(</span><span style="color: #4444FF">(</span><span style="color: #2040a0">string</span><span style="color: #4444FF">)</span><span style="color: #2040a0">parameterDescription</span>.<span style="color: #2040a0">Value</span><span style="color: #4444FF">)</span>.<span style="color: #2040a0">Trim</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">myProductDetails</span><span style="color: #4444FF">;</span>
        <span style="color: #4444FF"><strong>}</strong></span>

        

        <span style="color: #008000">//*******************************************************</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// ProductsDB.GetMostPopularProductsOfWeek() Method &lt;a name=&quot;GetMostPopularProductsOfWeek&quot;&gt;&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// The GetMostPopularProductsOfWeek method returns a struct containing a </span>
        <span style="color: #008000">// forward-only, read-only DataReader containing the most popular products </span>
        <span style="color: #008000">// of the week within the AdventureWorks database.  </span>
        <span style="color: #008000">// The SQLDataReaderResult struct also returns the</span>
        <span style="color: #008000">// SQL connection, which must be explicitly closed after the</span>
        <span style="color: #008000">// data from the DataReader is bound into the controls.</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// Other relevant sources:</span>
        <span style="color: #008000">//     + &lt;a href=&quot;usp_ProductsMostPopular.htm&quot; style=&quot;color:green&quot;&gt;usp_ProductsMostPopular Stored Procedure&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">//*******************************************************</span>

        <span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">SqlDataReader</span> <span style="color: #2040a0">GetMostPopularProductsOfWeek</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span> <span style="color: #4444FF"><strong>{</strong></span>

            <span style="color: #008000">// Create Instance of Connection and Command Object</span>
            <span style="color: #2040a0">SqlConnection</span> <span style="color: #2040a0">myConnection</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlConnection</span><span style="color: #4444FF">(</span><span style="color: #2040a0">Properties</span>.<span style="color: #2040a0">Settings</span>.<span style="color: #2040a0">Default</span>.<span style="color: #2040a0">ConnectionString</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">SqlCommand</span> <span style="color: #2040a0">myCommand</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlCommand</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;usp_ProductsMostPopular&quot;</span>, <span style="color: #2040a0">myConnection</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Mark the Command as a SPROC</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">CommandType</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CommandType</span>.<span style="color: #2040a0">StoredProcedure</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Execute the command</span>
            <span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">Open</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">SqlDataReader</span> <span style="color: #2040a0">result</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">ExecuteReader</span><span style="color: #4444FF">(</span><span style="color: #2040a0">CommandBehavior</span>.<span style="color: #2040a0">CloseConnection</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Return the datareader result</span>
            <span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">result</span><span style="color: #4444FF">;</span>
        <span style="color: #4444FF"><strong>}</strong></span>

        <span style="color: #008000">//*******************************************************</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// ProductsDB.SearchProductDescriptions() Method &lt;a name=&quot;SearchProductDescriptions&quot;&gt;&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// The SearchProductDescriptions method returns a data set containing</span>
        <span style="color: #008000">// a list of all</span>
        <span style="color: #008000">// products whose name and/or description contains the specified search</span>
        <span style="color: #008000">// string. </span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// Other relevant sources:</span>
        <span style="color: #008000">//     + &lt;a href=&quot;usp_ProductSearch.htm&quot; style=&quot;color:green&quot;&gt;usp_ProductSearch Stored Procedure&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">//*******************************************************</span>

        <span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">DataSet</span> <span style="color: #2040a0">SearchProductDescriptions</span><span style="color: #4444FF">(</span><span style="color: #2040a0">string</span> <span style="color: #2040a0">searchText</span>, <span style="color: #2040a0">string</span> <span style="color: #2040a0">language</span><span style="color: #4444FF">)</span> <span style="color: #4444FF"><strong>{</strong></span>

            <span style="color: #008000">// Create Instance of Connection and Command Object</span>
            <span style="color: #2040a0">SqlConnection</span> <span style="color: #2040a0">myConnection</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlConnection</span><span style="color: #4444FF">(</span><span style="color: #2040a0">Properties</span>.<span style="color: #2040a0">Settings</span>.<span style="color: #2040a0">Default</span>.<span style="color: #2040a0">ConnectionString</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">SqlDataAdapter</span> <span style="color: #2040a0">myCommand</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlDataAdapter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;usp_ProductSearch&quot;</span>, <span style="color: #2040a0">myConnection</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Mark the Command as a SPROC</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">SelectCommand</span>.<span style="color: #2040a0">CommandType</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CommandType</span>.<span style="color: #2040a0">StoredProcedure</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Add Parameters to SPROC</span>
            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterSearch</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@Search&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">255</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterSearch</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">searchText</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">SelectCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterSearch</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

			<span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterLanguage</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@Language&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NChar</span>, <span style="color: #FF0000">2</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">parameterLanguage</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">language</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">SelectCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterLanguage</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

			<span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterCurrencyCode</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@Culture&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">10</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">parameterCurrencyCode</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CultureInfo</span>.<span style="color: #2040a0">CurrentUICulture</span>.<span style="color: #2040a0">Name</span><span style="color: #4444FF">;</span>
			<span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">SelectCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterCurrencyCode</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>



            <span style="color: #008000">// Execute the command</span>
            <span style="color: #2040a0">DataSet</span> <span style="color: #2040a0">result</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">DataSet</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;SearchResults&quot;</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">result</span>.<span style="color: #2040a0">Locale</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CultureInfo</span>.<span style="color: #2040a0">InvariantCulture</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Fill</span><span style="color: #4444FF">(</span><span style="color: #2040a0">result</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>		
			<span style="color: #008000">// If we get no hits, then try English</span>
			<span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">result</span>.<span style="color: #2040a0">Tables</span><span style="color: #4444FF">[</span><span style="color: #FF0000">0</span><span style="color: #4444FF">]</span>.<span style="color: #2040a0">Rows</span>.<span style="color: #2040a0">Count</span> <span style="color: #4444FF">=</span><span style="color: #4444FF">=</span> <span style="color: #FF0000">0</span><span style="color: #4444FF">)</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">language</span> <span style="color: #4444FF">!</span><span style="color: #4444FF">=</span> <span style="color: #444444">&quot;EN&quot;</span><span style="color: #4444FF">)</span> <span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">SearchProductDescriptions</span><span style="color: #4444FF">(</span><span style="color: #2040a0">searchText</span>, <span style="color: #444444">&quot;EN&quot;</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>
			
            <span style="color: #008000">// Return the DataSet result</span>
            <span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">result</span><span style="color: #4444FF">;</span>
        <span style="color: #4444FF"><strong>}</strong></span>
    <span style="color: #4444FF"><strong>}</strong></span>
<span style="color: #4444FF"><strong>}</strong></span>
</pre>
